Methods and systems for optimizing text searches over structured data in a multi-tenant environment

ABSTRACT

Search systems and methods for searching structured data and enhancing the relevancy of search results provided to a requesting user or application. Enhanced search results are provided by searching a cache of a user&#39;s most recently used items to eliminate or reduce search indexing latency. Also, more relevant search results are provided by re-ranking results based on use history, data security models and: or hierarchical user role models. Further, enhanced search results are provided by including user information in the search index

CROSS-REFERENCE TO RELATED APPLICATIONS

This application is a continuation of U.S. application Ser. No.13/589,011, filed Aug. 17, 2012, which is a continuation of U.S.application Ser. No. 13/292,025, filed Nov. 8, 2011, and a continuationof U.S. application Ser. No. 13/292,011, filed Nov. 8, 2011, which arecontinuations of U.S. application Ser. No. 11/293,857, filed Dec. 2,2005, the entire contents of which are incorporated herein by reference.

BACKGROUND OF THE INVENTION

The present invention relates generally to systems and methods forsearching data, and more particularly to systems and methods forenhancing search results and text searches over structured data in a webapplication.

Due to the popularity and accuracy of current web search technology,users have come to expect quick up-to--date presentation of searchresults with the most relevant results presented at or near the top ofthe search results page. Web applications inevitably come with a similarset of expectations. Although in many regards a comparison is faulty,for example a Web application's data set looks very different comparedto most web pages, with the exception of attachments, documents, andnotes. Regardless of differences, customers almost certainly don'trealize or care about this, and expect the same functionality out of aweb application-based search. Despite all of the differences, there ismuch in common from an input box available on every page to how it isused primarily in order to find a specific record. This is in fact acommon usage pattern of web search known as a navigational search and itis something web search engines are quite good at. One of the reasonsthey excel in this area is because they use other information besidesthe text on the web page itself in order to do the scoring (link text isone good example of this).

The ordering of search results in web applications may not always rankthe most relevant results at or near the top of the results page. Searchresults are typically ranked based solely on a “relevancy” score givenby the search engine. One example of a useful search engine for use inweb applications is Lucene from The Apache Software Foundation. Luceneis a text search engine library written in Java, and is suitable fornearly any application that requires full-text search. With Lucene, forexample, the score is calculated using a standard information retrievalalgorithm based on many factors. While this score may be quite useful inthe overall rankings, the search engine doesn't take many factorsoutside the scope of the index into consideration.

Although the score provided by the search engine is quite useful, itdoes have limitations. Because a search is the most common means ofend-user navigation to a specific record in a multi-tenant databasesystem, such as that provided by salesforce.com, it is desirable toprovide more relevant results in response to a search request, andthereby increase end-user productivity and satisfaction with the searchfunctionality. This would also reduce the load on the system if userscan find the record they want without having to go to the detail pagesof multiple results.

Also, in systems where searching of structured data is implemented, suchas in a multi-tenant database system, search indexing latency can oftenbe a problem, especially where a user who recently added or modifieddata immediately searches for items using a term that should return arecently modified data entry. In the salesforce.com system, for example,search queries are run against a search index that is a replica of anorganization's data. As organization data is added or changed, abackground process (a search indexer) asynchronously updates the searchindex. Under peak system load, the volume of data change in the systemmay be so high that the search index update process can run behind,e.g., 2 to 5 minutes or more behind. As a result, there may be a lagtime, e.g., 2 to 5 minutes or more, between the time data is entered orchanged in the system and the time it may appear in search results. Thisis especially inconvenient for users when they make a change to the data(e.g., modify, add, delete) then immediately search for the data and areunable to find it because of search indexing latency.

Therefore it is desirable to provide search systems and methods thatovercome the above and other problems. For example, it is desirable toprovide search systems and methods that eliminate or reduce searchindexing latency. It is also desirable to provide search systems andmethods that enhance the relevancy of results returned.

BRIEF SUMMARY OF THE INVENTION

The present invention provides search systems and methods for searchingstructured data and enhancing the relevancy of search results providedto a requesting user or application.

According to the present invention, enhanced search results are providedby searching a cache of a user's most recently used items to eliminateor reduce search indexing latency. Also, more relevant search resultsare provided by re-ranking results based on use history, data securitymodels and/or hierarchical user role models. Further, enhanced searchresults are provided by including user information in the search index.

According to one aspect of the present invention, a method is providedfor performing a text search over structured data in a database system.The method typically includes formulating a search query including afirst search parameter, sending the search query to a query server,searching indexed data records in the database system using the firstsearch parameter, and returning a plurality of matching records andassociated relevancy scores. The method also typically includesmodifying the relevancy scores based on activity data associated withthe matching records, and ordering the matching records in an orderbased on the modified relevancy scores.

According to another aspect of the present invention, a method isprovided for reducing or eliminating the effects of indexing latencywhen performing a text search over structured data in a database system,wherein an indexing process asynchronously indexes the database as usersadd or modify records in the database. The method typically includesreceiving a search request including a search parameter from a firstuser system, and performing a first search over indexed data records inthe database system using the search parameter. The method alsotypically includes performing a second search, using the searchparameter, on a data structure containing a copy of the most recentdatabase records that have been modified by the first user system ormodified in response to a request from the first user system to add ormodify data, and providing the results of the first search and thesecond search to the first user system. In certain aspects, the firstand second searches may be performed sequentially or simultaneously.

According to yet another aspect of the present invention, a method isprovided for performing a backup search in a database system when asearch index query over structured data in a database system times-out.The method typically includes formulating a search query by anapplication server, the search query including a first search parameter,and sending the search query to a query server to search over indexeddata records in the database system. If no response to the search queryis received within a specified timeout period, the method typicallyincludes automatically performing a database lookup using the searchparameter in a search name table that stores name information for dataobjects in the database system.

According to yet another aspect of the present invention, a method isprovided for performing a text search over structured data in a databasesystem. The method typically includes receiving a search requestincluding a search parameter from a first user system, formulating asearch query including the first search parameter and user information,and sending the search query to a query server. The method alsotypically includes searching indexed data records in the database systemusing the first search parameter and the user information, wherein theindexed data records include at least one user information field, andreturning records matching on the search parameter, and a relevancyscore for each returned record, wherein relevancy scores of recordsmatching on the search parameter and matching on the user informationfield are higher than the relevancy scores for matching records notmatching on the user information field. In certain aspects, the at leastone user information field includes a user ID field and/or a user rolefield, and the user information in the search query includes one or bothof a user ID and a role of the user from which the search requestoriginated.

According to a further aspect of the present invention, a multi-tenantdatabase system is provided which implements one or more of the methodsof performing a text search over structured data, reducing oreliminating the effects of indexing latency, and performing a backupsearch as discussed and described herein.

Reference to the remaining portions of the specification, including thedrawings and claims, will realize other features and advantages of thepresent invention. Further features and advantages of the presentinvention, as well as the structure and operation of various embodimentsof the present invention, are described in detail below with respect tothe accompanying drawings. In the drawings, like reference numbersindicate identical or functionally similar elements.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 illustrates an environment wherein a multi-tenant database systemmight be used.

FIG. 2 illustrates elements of FIG. 1 and various interconnections inmore detail.

FIG. 3 illustrates the architecture of a search system 200 according toone embodiment.

FIG. 4 shows an example of a table definition of an MRU according to oneembodiment.

FIG. 5 a illustrates a search name lookup schema, and FIG. 5 billustrates an example of a fallback search query, according to oneembodiment.

FIG. 6 illustrates an example of a query processing flow according toone embodiment.

FIG. 7 illustrates an example of a user interface display of a sidebarsearch page according to the present invention.

FIG. 8 illustrates an example of a user interface display of a page forconducting an advanced search according to the present invention.

FIG. 9 illustrates an example of a user interface display of a searchresults page according to the present invention.

FIG. 10 illustrates an example of database (DB) fields and indexedfields for a standard entity according to the present invention.

DETAILED DESCRIPTION OF THE INVENTION

The present invention provides systems and methods for searchingstructured data and enhancing the relevancy of search results providedto a requesting user or application. In particular, the presentinvention provides systems and methods for searching structured datastored in a multi-tenant database system and for providing enhancedsearch results by removing search indexing latency. Also, the presentinvention, in certain aspects, provides systems and methods forpresenting more relevant search results by re-ranking results based onuse history, data security models and/or hierarchical user role models.In certain aspects, the present invention provides enhanced searchresults by including user information in the search index.

FIG. 1 illustrates an environment wherein a multi-tenant database systemmight be used. As illustrated in FIG. 1 (and in more detail in FIG. 2)any user systems 12 might interact via a network 14 with a multi-tenantdatabase system (MTS) 16. The users of those user systems 12 might beusers in differing capacities and the capacity of a particular usersystem 12 might be entirely determined by permissions (permissionlevels) for the current user. For example, where a salesperson is usinga particular user system 12 to interact with MTS 16, that user systemhas the capacities allotted to that salesperson. However, while anadministrator is using that user system to interact with MTS 16, thatuser system has the capacities allotted to that administrator. Insystems with an hierarchical role model, users at one permission levelmay have access to applications, data and database informationaccessible by a lower permission level user, but may not have access tocertain applications, database information and data accessible by a userat a higher permission level. Thus, different users will have differentcapabilities with regard to accessing and modifying application anddatabase information, depending on a user's security or permissionlevel.

Network 14 can be a LAN (local area network), WAN (wide area network),wireless network, point-to-point network, star network, token ringnetwork, hub network, or other configuration. As the most common type ofnetwork in current use is a TCP/IP (Transfer Control Protocol andInternet Protocol) network such as the global internetwork of networksoften referred to as the “Internet” with a capital “I,” that will beused in many of the examples herein. However, it should be understoodthat the networks that the present invention might use are not solimited, although TCP/IP is the currently preferred protocol.

User systems 12 might communicate with MTS 16 using TCP/1P and, at ahigher network level, use other common Internet protocols tocommunicate, such as HTTP, FTP, AFS, WAP, etc. As an example, where HTTPis used, user system 12 might include an HTTP client commonly referredto as a “browser” for sending and receiving HTTP messages to and from anHTTP server at NITS 16. Such HTTP server might be implemented as thesole network interface between MTS 16 and network 14, but othertechniques might be used as well or instead. In some implementations,the interface between MTS 16 and network 14 includes load sharingfunctionality, such as round-robin HTTP request distributors to balanceloads and distribute incoming HTTP requests evenly over a plurality ofservers. Preferably, each of the plurality of servers has access to theMTS's data, at least as for the users that are accessing that server.

In one aspect, the system shown in FIG. 1 implements a web-basedcustomer relationship management (CRM) system. For example, in oneaspect, MTS 16 can include application servers configured to implementand execute CRM software applications as well as provide related data,code, forms, web pages and other information to and from user systems 12and to store to, and retrieve from, a database system related data,objects and web page content. With a multi-tenant system, tenant data ispreferably arranged so that data of one tenant is kept logicallyseparate from that of other tenants so that one tenant does not haveaccess to another's data, unless such data is expressly shared. Inpreferred aspects, system 16 implements applications other than, or inaddition to, a CRM application. For example, system 16 may providetenant access (0 multiple hosted (standard and custom) applications,including a CRM application.

One arrangement for elements of MTS 16 is shown in FIG. 1, including anetwork interface 20, storage 22 for tenant data, storage 24 for systemdata accessible to MTS 16 and possibly multiple tenants, program code 26for implementing various functions of MTS 16, and a process space 28 forexecuting MTS system processes and tenant-specific processes, such asrunning applications as part of an application hosting service.Additional processes that may execute on MTS 16 include databaseindexing processes.

Several elements in the system shown in FIG. 1 include conventional,well-known elements that need not be explained in detail here. Forexample, each user system 12 could include a desktop personal computer,workstation, laptop, PDA, cell phone, or any wireless access protocol(WAP) enabled device or any other computing device capable ofinterfacing directly or indirectly to the Internet or other networkconnection. User system 12 typically runs an HTTP client, e.g., abrowsing program, such as Microsoft's Internet Explorer browser,Netscape's Navigator browser, Opera's browser, or a WAP-enabled browserin the case of a cell phone, PDA or other wireless device, or the like,allowing a user (e.g., subscriber of the multi-tenant database system)of user system 12 to access, process and view information, pages andapplications available to it from MTS 16 over network 14. Each usersystem 12 also typically includes one or more user interface devices,such as a keyboard, a mouse, touch screen, pen or the like, forinteracting with a graphical user interface (GUI) provided by thebrowser on a display (e.g., monitor screen, LCD display, etc.) inconjunction with pages, forms, applications and other informationprovided by MTS 16 or other systems or servers. For example, the userinterface device can be used to access data and applications hosted byMTS 16, and to perform searches on stored data, and otherwise allow auser to interact with various GUI pages that may be presented to a user.

As discussed above, the present invention is suitable for use with theInternet, which refers to a specific global internetwork of networks.However, it should be understood that other networks can be used insteadof the Internet, such as an intranet, an extranet, a virtual privatenetwork (VPN), a non-TCP/IP based network, any LAN or WAN or the like.

According to one embodiment, each user system 12 and all of itscomponents are operator configurable using applications, such as abrowser, including computer code run using a central processing unitsuch as an Intel Pentium processor or the like. Similarly, MTS 16 (andadditional instances of MTS's, where more than one is present) and allof their components might be operator configurable using application(s)including computer code run using a central processing unit such as anIntel Pentium processor or the like, or multiple processor units.Computer code for operating and configuring MTS 16 to intercommunicateand to process web pages, applications and other data and media contentas described herein is preferably downloaded and stored on a hard disk,but the entire program code, or portions thereof, may also be stored inany other volatile or non-volatile memory medium or device as is wellknown, such as a ROM or RAM, or provided on any media capable of storingprogram code, such as a compact disk (CD) medium, digital versatile disk(DVD) medium, a floppy disk, and the like. Additionally, the entireprogram code, or portions thereof, may be transmitted and downloadedfrom a software source, e.g., over the Internet, or from another server,as is well known, or transmitted over any other conventional networkconnection as is well known (e.g., extranet, VPN, LAN, etc.) using anycommunication medium and protocols (e.g., TCP/IP, HTTP, HTTPS, Ethernet,etc.) as are well known. It will also be appreciated that computer codefor implementing aspects of the present invention can be implemented inany programming language that can be executed on a server or serversystem such as, for example, in C, C++, HTML, any other markup language,Java, JavaScript, any other scripting language such as VBScript, andmany other programming languages as are well known.

According to one embodiment, each MTS 16 is configured to provide webpages, forms, applications, data and media content to user systems 12 tosupport the access by user systems 12 as tenants of MTS 16. As such, MTS16 provides security mechanisms to keep each tenant's data separateunless the data is shared. If more than one MTS is used, they may belocated in close proximity to one another (e.g., in a server farmlocated in a single building or campus), or they may be distributed atlocations remote from one another (e,g., one or more servers located incity A and one or more servers located in city B). As used herein, eachMTS could include one or more logically and/or physically connectedservers distributed locally or across one or more geographic locations.Additionally, the term “server” is meant to include a computer system,including processing hardware and process space(s), and an associatedstorage system and database application (e.g., OODBMS or RDBMS) as iswell known in the art, it should also be understood that “server system”and “server” are often used interchangeably herein. Similarly, thedatabases described herein can be implemented as single databases, adistributed database, a collection of distributed databases, a databasewith redundant online or offline backups or other redundancies, etc.,and might include a distributed database or storage network andassociated processing intelligence.

FIG. 2 illustrates elements of MTS 16 and various interconnections inmore detail In this example, the network interface is implemented as oneor more HTTP application servers 100. Also shown is system process space102 including individual tenant process spaces 104, a system database106, tenant database(s) 108 and a tenant management process space 110.Tenant database 108 might be divided into individual tenant storageareas 112, which can be either a physical arrangement or a logicalarrangement. Within each tenant storage area 112., user storage 114might similarly be allocated for each user. For example, a copy of auser's most recently used (MRU) items, as will be discussed below inmore detail, might be stored to user storage area 114. Similarly, a copyof MRU items for an entire organization that is a tenant might be storedto tenant storage area 112.

It should also be understood that each application server 100 may becommunicably coupled to database systems, e.g., system database 106 andtenant database(s) 108, via a different network connection. For example,one server 100 ₁ might be coupled via the Internet. 14, another server100 _(N-1) might be coupled via a direct network link, and anotherserver 100 _(N) might be coupled by yet a different network connection.Transfer Control Protocol and Internet Protocol (TCP/IP) are preferredprotocols for communicating between servers 100 and the database system,however, it will be apparent to one skilled in the art that othertransport protocols may be used to optimize the system depending on thenetwork interconnect used.

In preferred aspects, each application server 100 is configured tohandle requests for any user associated with any organization that is atenant. Because it is desirable to be able to add and remove applicationservers from the server pool at any time for any reason, there ispreferably no server affinity for a user and/or organization to aspecific application server 100. In one embodiment, therefore, aninterface system (not shown) implementing a load balancing functione.g., an F5 Big-IP load balancer) is communicably coupled between theservers 100 and the user systems 12 to distribute requests to theservers 100. In one aspect, the load balancer uses a least connectionsalgorithm to route user requests to the servers 100. Other examples ofload balancing algorithms, such as round robin and observed responsetime, also can be used. For example, in certain aspects, threeconsecutive requests from the same user could hit three differentservers, and three requests from different users could hit the sameserver. In this manner, MTS 16 is multi-tenant, wherein MTS 16 handlesstorage of, and access to, different objects, data and applicationsacross disparate users and organizations.

As an example of storage, one tenant might be a company that employs asales force where each salesperson uses MTS 16 to manage their salesprocess. Thus, a user might maintain contact data, leads data, customerfollow-up data, performance data, goals and progress data, etc., allapplicable to that user's personal sales process (e.g., in tenantdatabase 108). In the preferred MTS arrangement, since all of this dataand the applications to access, view, modify, report, transmit,calculate, etc., can be maintained and accessed by a user system havingnothing more than network access, the user can manage his or her salesefforts and cycles from any of many different user systems. For example,if a salesperson is visiting a customer and the customer has Internetaccess in their lobby, the salesperson can obtain critical updates as tothat customer while waiting for the customer to arrive in the lobby.

While each user's data might be separate from other users' dataregardless of the employers of each user, some data might beorganization-wide data shared or accessible by a plurality of users orall of the users for a given organization that is a tenant. Thus, theremight be some data structures managed by MTS 16 that are allocated atthe tenant level while other data structures might be managed at theuser level. Because an MTS might support multiple tenants includingpossible competitors, the MTS should have security protocols that keepdata, applications and application use separate. Also, because manytenants will opt for access to an MTS rather than maintain their ownsystem, redundancy, up-time and backup are additional critical functionsand need to be implemented in the MTS.

In addition to user-specific data and tenant-specific data. MTS 16 mightalso maintain system level data usable by multiple tenants or otherdata. Such system level data might include industry reports, news,postings, and the like that are sharable among tenants.

In certain aspects, client systems 12 communicate with applicationservers 100 to request and update system-level and tenant-level datafrom MTS 16 that may require one or more queries to database system 106and/or database system 108. MTS 16 (e.g., an application server 100 inMTS 16) generates automatically one or more SQL statements (the SQLquery) designed to access the desired information.

Each database can generally be viewed as a collection of objects, suchas a set of logical tables, containing data fitted into predefinedcategories. A “table” is one representation of a data object, and isused herein to simplify the conceptual description of objects and customobjects according to the present invention. It should be understood that“table”, “entity” and “object” may be used interchangeably herein. Eachtable generally contains one or more data categories logically arrangedas columns or fields in a viewable schema. Each row or record of a tablecontains an instance of data for each category defined by the fields.For example, a CRM database may include a table that describes acustomer with fields for basic contact information such as name,address, phone number, fax number, etc. Another table might describe apurchase order, including fields for information such as customer,product, sale price, date, etc. In some multi-tenant database systems,standard entity tables might be provided for use by all tenants. For CRMdatabase applications, such standard entities might include tables forAccount, Contact, Lead and Opportunity data, each containing pre-definedfields.

In some multi-tenant database systems, such as the salesforce.comsystem, tenants may be allowed to create and store custom objects, orthey may be allowed to customize standard entities or objects, forexample by creating custom fields for standard objects, including customindex fields. U.S. patent application Ser. No. 10/817,161, filed Apr. 2,2004, titled “Custom Entities and Fields In a Multi-Tenant DatabaseSystem”, and which is hereby incorporated by reference, teaches systemsand methods for creating custom objects as well as customizing standardobjects in a multi--tenant database system.

Searching

FIG. 3 illustrates the architecture of a search system 200 according toone embodiment. In one aspect, the search system of FIG. 3 isimplemented in the multi-tenant database system 16 of FIG. 1. As shown,search system 200 includes one or a plurality of query servers 210(“queriers”) communicably coupled to application server(s) 100 and adatabase system 220 (e,g., system database 106 and/or tenant database108 of FIG. 2). Queriers 210 are also communicably coupled with indexers230. Indexers 230 are indexing processes that query and index thedatabase 220. It should be appreciated that an indexer 230 can beimplemented in a separate server or computer system or it may beimplemented or may execute in a query server 210 or in an applicationserver 100, and multiple indexers may execute in different systems.Similarly, a querier 210 may be implemented or may execute in a separateserver or computer system or it may be implemented or may execute in anapplication server 100.

In typical operation, an application server 100 sends a query request toa querier 210. Where multiple queriers 210 are present, a load balancing(e.g., round robin) process may be implemented to direct the query to aspecific querier 210. Typically, the query request is in the form of anHTTP request including a search parameter (e.g., term or terms to besearched), although other forms and protocols may be used. The querier210 that receives the query performs a search by reading the searchindex(es) created by the indexers 230 to determine one or more matcheswith the term(s) provided. The results are returned to the requestingapplication server 100. The results are typically returned with a base“relevancy” score as determined by the underlying search engine process,e.g., Lucene. Typically, a pre-designated upper limit of N (e.g., 1000,2000, 5000, etc.) search index results is provided to the querier 210 bythe search engine in order of relevancy. As will be described in moredetail below, the relevancy of search results may be enhanced accordingto various embodiments and aspects of the present invention.

As data is added or changed in the database, a search indexer 230 (abackground process) asynchronously updates the search index. In oneaspect, a search index is stored to the database 220, but it may bestored to a separate storage system. In a multi-tenant databaseenvironment, in one aspect, a separate index is created for eachorganization. Each organization may have one or more separate indexers230 executing on its behalf, or one or more indexers 230 may be sharedbetween organizations. In one aspect, for example, one indexer executesper instance per indexing host.

Upon a database insert or update event, a timestamp is assigned to theadded or modified data. For example, in one aspect, modified or addeddata is copied to a “to be indexed” table including fields for data typeand timestamp. This timestamp indicates the date (and time) from whichall data in the table needs to be indexed. As the indexer executes, todetermine the set of data that needs to be indexed, the indexer queriesa table's data looking for rows which have a system_modstamp>=thetimestamp in the to be indexed table, where system_modstamp is a fieldthat includes the time that the system last modified or changed the datarow. The returned rows represent the data that needs indexing. In oneaspect, chunks of row IDs are read from the “to be indexed” table. Foreach ID, the indexer queries the database for the source row of data andcopies all the relevant fields (e.g., fields of a type that may beindexed, such as text, phone number, e-mail address, etc.) to an indexfile in the appropriate index format. In one aspect, user informationsuch as the record owner field and/or role hierarchy informationfield(s) are also indexed to provide more relevantly ordered results inresponse to a query including the user ID and/or user role as searchparameters as will be discussed below.

As an index for an organization grows, it may be partitioned. Forexample, a large organization may have several physical partitions inthe search index. Additionally, depending on the load on the system,there may be a latency between when data is added or modified in thedatabase and when it appears in an index. To hide the impact of this lagtime from the user, in one aspect, the user's most recently used recordsand objects are tracked or cached for inclusion in the search results aswill be discussed in more detail below.

Most Recently used Cache

Search queries run against a search index that is built from anorganization's data. As above, as organization data is added or changed,a background process (the search indexer) asynchronously updates thesearch index. Under peak system load, the volume of data change in thesystem is such that the search index update process can run behind,e.g., lag up to 5 minutes or more. This means there can be a lag in timebetween the time data is entered or changed in the system and the timeit appears in search results. This is especially inconvenient for userswhen they make a change then immediately search for it and are unable tofind it because of search indexing latency.

To optimize the search results, in one embodiment, the system maintainsa per-user cache of recent objects the user has added or changed. Forexample, the cache may hold the last N (e.g., any number ranging up to50 or 100 or more) objects the user has added or modified, or it mayhold all objects a user has added or modified within a specified timeperiod, e.g., within the last 2 to 5 or more minutes. With reference toFIG. 3, this cache will be referred to as the Most Recently Used list orMRU 240. In certain aspects, the MRU 240 is stored in the database 220keyed by organization id and user id. Alternatively, the MRU 240 may bestored separately from the database 220. FIG. 4 shows an example of atable definition of an MRU according to one embodiment. In one aspect,when a search query is received, the system checks the search index forrelevant “matching” results. The system also searches the MRU formatching objects and returns MRU search results. For example, anapplication server 100 may itself search or scan the MRU to includerelevant MRU items in the search results after it has receivedindex-matching results from a querier 210, or a querier 210 may searchor scan the MRU and incorporate relevant MRU items in the search resultsbefore providing the results to the application server 100. The indexsearch results and MRU search results are returned to, and displayed at,a requesting user system 12. In one aspect, the index search results andthe MRU search results are distinguished or displayed separately.

In another aspect, the results of the MRU search are mixed in with theother search results, so users won't know explicitly that some resultscame from the search index and some from the MRU. This is useful incases where, for example, only one term (e.g., the first term) ofmultiple search terms is used as the search parameter. For example, theMRU results may be wrong for the search because if it only uses thefirst term: e.g., if they search for “scott h”, the MRU search justlooks for “scott” and so would find scott yancey. This may confuse theuser so it is desirable to limit the MRU searching to just the unindexedtime. In one aspect, only one term, such as the first term, is used inthe search of the MRU because this simple query can be implemented usinga database lookup instead of a true search index.

Thus, the present invention, advantageously allows a user who adds oredits data and then searches for it to see the recently added or changeddata in their search results even if the search indexing process isbehind or lagging.

According to another aspect, rather than searching the user's MRU forunindexed data, the system searches all or a subset of the MRU for anentire organization with which the user is associated, e.g., search allthe unindexed data for the entire organization. However, searching theMRU for the entire organization may be less desirable than searchingonly the user's MRU, or a smaller subset of an organization's MRU, incertain instances. For example, the set of unindexed data for an entireorganization could be very large, significantly slowing down the userssearch. Additionally, the search over unindexed data may use adifferent, simpler algorithm that results in different results than thenormal search process. It is desirable to minimize the amount ofunindexed data that could potentially show up in the search results. Theuser's MRU data typically proves to be the best set of highly relevantdata; it is more likely that the user is searching for something theyrecently modified compared to all recent modifications for the entireorganization with which the user is associated.

Search Result Relevancy Based on Security and Rule Hierarchy Models

In certain aspects, search queries are run against an organization'ssearch index which contains text from all the organization's records inthe database. For common search terms such as “Jim” or “Main Street”,the index may return thousands of matching records, many of which maynot be visible to the user based on sharing and security settings. Forexample, the user may not have access to many of the records or rowsreturned based on a security model, e.g., hierarchical permissionlevels. Also, most of these thousands of results are likely notrelevant, or at least less relevant, to the user.

According to one embodiment, a security check is performed wherein thesearch results are filtered based on sharing rules after the results arereturned from the search engine. In one aspect, application server 100queries the database for sharing rules, and filters the search resultsbased on the sharing rules. In another aspect, a querier 210 queries thedatabase for sharing rules and filters the search results beforeproviding the search results to the requesting application server 100.To limit the database performance impact of querying for sharing rules,an upper limit of N (e.g., 1000, 2000, 5000, etc.) search index resultsis established for which the system will attempt to apply the securitycheck. If the search results from the index contain more than N entries,it is very important that they are ordered by relevance to the user sothat the results the user is most likely interested in are included inthe N entries that continue to the security check and eventually arepresented to the user.

According to one embodiment, the search results are optimized by addingthe record owner field to the index as a separate search index field. Inthis aspect, the application server 100 can also include the user's IDsas a search parameter so that matches on the owner field are boosted inthe search results relevancy scores. Since the user always has securityaccess to anything they own, this would dramatically increase thelikelihood that a query server would return hits that the user hasaccess to in the first N results.

Furthermore, in another embodiment, search results are optimized using auser role hierarchy model. In systems implementing a role hierarchy,such as in the salesforce.com system, users may have access to recordsowned by their subordinates in the role hierarchy. In this embodiment,the role of the record owner is added to the search index as a separatesearch index field. At query time, the search query is expanded toinclude a boost on all records owned by the user's subordinates in therole security hierarchy to further enhance likelihood of obtainingrelevant matches. For example, where a user at level 1 performs asearch, records owned by that user that match the search term(s) areboosted in the relevancy score above those matching records not owned bythat user. Further, records owned by a subordinate user at level 2 thatmatch the search term(s) are also boosted above those matching recordsnot owned by those users. It should be appreciated that this aspect isnot limited to a role hierarchy; it applies to any other userhierarchies that may be implemented in the system, such as a salesterritory hierarchy or any other security hierarchy. Further, it shouldbe appreciated that a user may specifically identify a user role as aspecific search parameter, assuming of course that the identified roleis not a superior role to the user's role in the hierarchy.

Re-Ranking Search Results Based on Entity History

According to one embodiment, entity (i.e., standard or custom databaseobject or table) history is used to modify a search result's relevancyscore. For example, entity history may indicate that a data row is moreactive than others within the search result set and that it shouldtherefore be moved to a higher position in the search results set. Datarows that have more activity associated with them (e.g., creatingfollow-up tasks for an account or logging a phone call with a contact)will likely be more active in the system and more relevant to the userperforming the search. Similarly, data that has been recently updated,even by another user, may be more relevant to the user performing thesearch.

In a first step of the re-ranking process, according to one embodiment,the search relevancy scores returned by the search engine, e.g., Lucene,are normalized. For example, the raw Lucene score can be any decimalnumber from 0 to M, e,g., 10 or more. The normalization functionconverts the raw score into a smaller (arbitrary) integer value, e.g., 0through 4. Next, a score is assigned to the data row based on anactivity measure. In one aspect, for example, a score is assigned to thedata row based on the values of last update and last activity fields.Last update is the last time a use modifies a given record (e.g.,updating an address on an account). Last activity is the last time auser performed any action associated with the data row (e.g., creating afollow up task, or logging a call). Thereafter, the normalized searchscore, the last update score and the last activity score are multipliedby a weight factor to arrive at a final relevancy score. The weightfactor can be an integer or a fraction of an integer. Search results arethen ordered with highest relevancy scores first and ultimatelypresented to the user. It should be understood that an applicationserver 100, a querier 210 or other system entity may implement there-ranking process.

Table 1, below, shows an example of re--ranking scores and weightsaccording to one aspect, although the exact values can be altered totune the relevancy. In the Table 1 example, last activity is given moreweight than last update, because typically most activity on an entitytakes place in its child records.

TABLE 1 Entity Formula and values Score Weight Search Score normalized0-4 3 Last Update 1 <1 hour old 4 <1 day old 3 <7 days old 2 <30 daysold 1 Older than 30 days 0 Last Activity 2 <1 hour old 4 <1 day old 3 <7days old 2 <30 days old 1 Older than 30 days 0 Not set 2 Tie Breaker N/AAccount name (lower case)

In Table 1, the entity can be any entity provided in the system such asstandard entities or custom entities. In the salesforce.com system, forexample, this could include such standard entities include Accounts,Leads and Contacts as well as custom entities.

Table 2 shows an example of search engine results and the order in whichthey currently might be displayed (raw score is the Lucene score):

TABLE 2 Account Name Raw Score Normalized Score Last Update LastActivity Re-ranked score Acme 1.30 4 2 years ago 2 years ago 12 + 0 + 0= 12 Acme East Co. 1.14 4 9 days ago 2 weeks ago 12 + 2 + 2 = 16 AcmeBrand Inc 1.11 4 30 minutes ago 10 minutes ago 12 + 4 + 8 = 24 Acmesoft0.72 3 8 days ago 2 days ago  9 + 1 + 4 = 14

In this example, after the re-ranking process described above withreference to the example of Table 1, the 3rd result would jump to thetop of the list with a re-ranked score of 24. Although this is acontrived example, one can see how the raw scores that are somewhatlumped close together may not be in a desired ordering, as a user ismore likely to be interested in the Acme Brand Inc, account, which hashad some recent activity, than the Acme account which has been dormantfor two years. In fact, in this case, the Acme account which previouslywould have been ranked at the top without re-ranking has fallen to thebottom of the list. Although the data set for this example is contrived,it shows the power of the re-ranking process of the present invention.

It should understood that any entity fields in addition to those fieldsthat are representative of an activity measure may be used in thecalculation of a final score and that various weightings may be providedto different fields as desired. As examples, an Opportunity entity inthe salesforce.com system might be scored with weights for fields suchas closed, close date and last activity as shown in the example below.

Opportunity Formula and values Score Weight Search Score normalized 0-46 Closed 4 open 1 closed 0 Close Date 1 Over 7 days in the past 1 Within7 days of today (past or future) 4 In the next 30 days 3 In the next 90days 2 Over 90 days, or not set 0 Last Activity 1 <1 hour old 4 <1 dayold 3 <7 days old 2 <30 days old 1 Older than 30 days 0 Not set 2 TieBreaker N/A Name (lower case)

Similarly, a Cases entity in the salesforce.com system might be scoredwith weights for fields such as escalated, closed, and last update asshown in the example below.

Cases Formula and values Score Weight Search Score normalized 0-4 4Escalated 4 Is escalated 1 Not escalated 0 Closed 3 open 1 closed 0 LastUpdate 1 <1 hour old 4 <1 day old 3 <7 days old 2 <30 days old 1 Olderthan 30 days 0 Not set 2 Tie Breaker N/A Case subject (lower case)

Fallback Search Mode

According to one aspect, if the application server 100 does not receivea response from a query server within a specified timeout period, itperforms a “fallback search”. For example, the query server may notrespond because it is overloaded with requests, or down for maintenance.In one aspect, a fallback search involves bypassing the search index andrunning a query against the database directly. FIG. 5 a illustrates a“Search Name Lookup” schema, and FIG. 5 b illustrates an example of afallback search query, according to one embodiment. The fallback searchqueries the “Search Name Lookup” table, which is a denormalized tablethat stores name information for all entities in the system. This maynot be as powerful as an index search as it only searches the namefield, but often will give the user useful information.

In one aspect, the search results returned from the fallback searchquery are also filtered against security permissions and then returnedto the user with a message explaining that the search results are notoptimized using the index.

Search Query Processing Flow

FIG. 6 illustrates an example of a search query process flow accordingto one embodiment of the present invention. In step 600, the applicationserver 100 formulates a query to be processed. The query formulated byapplication server 100 may be in response to a specific search requestreceived from a user system 12, e.g., including specific searchparameters such as a specific term or terms to be searched.Alternatively, the query formulated by application server 100 may begenerated automatically based on some other user input or system statusthat may not be a specific search request. For example, a query may beformulated as part of an automatic solution suggestion application or inresponse to information input from a form posted by a user. Theformulated query in certain aspects is in the form of a formatted HTTPmessage. In addition to a search parameter such as one or more terms tobe searched, the query in one aspect includes information about the user(e.g., user ID and/or the user's role in a role hierarchy) so as toimplicitly boost the relevancy of the results generated by the searchengine.

In step 610, the formulated search query is sent to a query server 210.In step 620, the query server 210 receives the search query and performsa search using the received parameters, e.g., by reading the indexacross a storage area network. For multi-partition indexes, this is donein parallel in one aspect. The top N matching results, including a“relevancy” score, are returned to the query server 210, includingresults that the user may not have security access to or permission toview.

In step 630, the search results are returned to the requestingapplication server 100. In one aspect, the top N matches, including arank and/or the “relevancy” score, are returned. Closer matches to thesearch term(s) will have a higher relevancy score.

In step 640, the search results are re-ranked based on activity, e.g.,based on history or activity information such as last update and lastactivity of a data item. In one aspect, this step is performed by theapplication server 100 after the results have been received from querier210. In another aspect, this step is performed by the query server 210prior to returning results to the application server 100.

In step 650,the search results are filtered based on securitypermissions. In one aspect, all data that matches the search parametersare returned to the application server 100, including the data that theuser may not have security access to or permission to view. In thisaspect, application server 100 performs step 650. In another aspect, thequery server performs step 650 and filters the N search results based onsecurity permissions of the user, and sends the filtered results to theapplication server 100.

In step 660, the MRU 240 is processed to determine items that match thesearch parameters. In one aspect, application server 100 performs theMRU search 660. In another aspect, a query server 210 performs the MRUsearch 660. The results of the MRU search are combined with the indexsearch results. In one aspect, MRU search 660 is performed substantiallysimultaneously with the search performed in step 620.

As will be appreciated, processing steps 640, 650 and 660 need not beperformed in any specific order. Further, each of processing steps 640,650 and 660 is optional; any combination of one or more of processingsteps 640, 650 and 660 may be performed by an application server 100and/or a query server 210 in any order.

In one embodiment, if the application server does not receive a responsefrom the query server within a specified timeout period, the applicationperforms a “fallback” search as described above. The timeout period maybe exceeded due to the query server being overloaded or the system beingdown for maintenance. In one aspect, the fallback search simulates indexsearching functionality by performing a database query against adenormalized search name lookup table. This may not be as powerful as anindex search, but will likely give the user useful information.

User Interface

In certain aspects, visual search tools are provided to a user to accessand search structured data stored in database 220. For example, agraphical user interface including pages with search links may bedisplayed to a user on a display of user system 12.

FIG. 7 illustrates an example of a user interface display of a sidebarsearch page 700 according to the present invention. Page 700 includes anentry box for receiving as user input one or more terms to be searched.Upon entry of a term or terms and selection of the search button, theterms) are sent to an application server 100, which then performs theappropriate search as discussed herein. Additionally, a link to an“advanced search” page (e.g., see FIG. 8) is presented on page 700. Inaddition to an advanced search page link, page 700 also includes sets oftabs (e.g., Home, Cases, Reports . . . ), where each tab represents auser interface into an element of an application or into a databaseobject. Selection of a tab provides a user access to the object orelement of the application represented by the tab. A tab set is a groupof related tabs that work as a unit to provide applicationfunctionality. In this manner, selection of the displayed tabs and tabsets by a user allows for convenient switching between applicationsand/or database objects as desired. U.S. patent application Ser. No.11/07.5,546, filed Mar. 8, 2005, titled “Systems and Methods forImplementing Multi-Application Tabs and Tab Sets,” which is herebyincorporated by reference, discusses tabs and tab sets in more detail.

FIG. 8 illustrates an example of a user interface display of a page 800for conducting an advanced search according to the present invention. Ascan be seen, in one aspect, the advanced search page 800 allows a userto enter a term or terms to be searched as well as to limit the searchto specific object types and/or specific entities by selecting thespecific objects and/or entities to be searched. Page 800 also allows auser to limit the search to only items that the user owns, such thatonly results that match on the user ID are returned.

FIG. 9 illustrates an example of a user interface display of a searchresults page 900 according to the present invention. Data for searchresults page 900 is provided to a user system 12 by application server100. In certain aspects, as shown, the results page presented to a userincludes search results organized by object in relevancy order. Inaddition to providing ordered search results, page 900 provides the userwith an ability to revise the search or to start a new search.

FIG. 10 illustrates an example of database (DB) fields and indexedfields for a standard entity according to the present invention. Incertain aspects, fields selected for indexing by an indexer can belimited differently for different types of searches performed, e.g.,sidebar search or advanced search.

While the invention has been described by way of example and in terms ofthe specific embodiments, it is to be understood that the invention isnot limited to the disclosed embodiments. To the contrary, it isintended to cover various modifications and similar arrangements aswould be apparent to those skilled in the art. Therefore, the scope ofthe appended claims should be accorded the broadest interpretation so asto encompass all such modifications and similar arrangements.

What is claimed is:
 1. A computer-implemented method of performing atext search over structured data in a database system, the methodcomprising: formulating a search query including a first searchparameter received from a user; sending the search query to a queryserver; searching indexed data records in the database system using thefirst search parameter; returning a ply plurality of matching records,based on the searching; wherein the matching records have associatedrelevancy scores and are ordered in an order that is based, at least inpart, on the relevancy scores; wherein the relevancy scores are based onactivity data associated with the matching records; wherein therelevancy scores are further based on the user from which the firstsearch parameter is received, such that the matching records that arereturned are personalized for the user from which the first searchparameter is received.
 2. The computer-implemented method of claim 1,wherein the formulating the search query is performed by an applicationserver in response to a specific search request receive( from a usersystem including the first search parameter.
 3. The computer-implementedmethod of claim 2, wherein the indexed data records include a user IDfield and/or a user role field, and the search query includes one orboth of a user ID and a role of the user from which the search queryoriginated.
 4. The computer-implemented method of claim 1, wherein theactivity data includes one of data in a last activity field and/or datain a last update field.
 5. The computer-implemented method of claim 1,further comprising displaying at least a portion of the matching recordsin the order,
 6. A system for performing a text search over structureddata a database system, comprising: a processor for: formulating asearch query including a first search parameter received from a user;sending the search query to a query server; searching indexed datarecords in the database system using the first search parameter;returning a plurality of matching records, based on the searching;wherein the matching records have associated relevancy scores and areordered in an order that is based, at least in part, on the relevancyscores; wherein the relevancy scores are based on activity dataassociated with the matching records; wherein the relevancy scores arefurther based on the user from which the first search parameter isreceived, such that the matching records that are returned arepersonalized for the user from which the first search parameter isreceived,
 7. The system of claim 6, wherein the formulating the searchquery is performed by an application server in response to a specificsearch request received from a user system including the first searchparameter.
 8. The system of claim 7, wherein the indexed data recordsinclude a user ID field and/or a user role field, and the search queryincludes one or both of a user ID and a role of the user from which thesearch query originated.
 9. The system of claim 6, wherein the activitydata includes one of data in a last activity field and/or data in a lastupdate field.
 10. The system of claim 6, wherein the processor isfurther for displaying at least a portion of the matching records in theorder.
 11. A computer program product embodied on a tangible computerreadable medium for performing a text search over structured data in adatabase system, comprising: computer code for formulating a searchquery including a first search parameter received from a user; computercode for sending the search query to a query server; computer code forsearching indexed data records in the database system using the firstsearch parameter; computer code for returning a plurality of matchingrecords, based on the searching; wherein the computer program product isoperable such that the matching records have associated relevancy scoresand are ordered in an order that is based, at least in part, on therelevancy scores; wherein the relevancy scores are based on activitydata associated with the matching records; wherein the computer programproduct is operable such that the relevancy scores are further based onthe user from which the first search parameter is received, such thatthe matching records that are returned are personalized for the userfrom which the first search parameter is received.
 12. The computerprogram product of claim 11, wherein the formulating the search query isperformed by an application server in response to a specific searchrequest received from a user system including the first searchparameter.
 13. The computer program product of claim 12, wherein theindexed data records include a user ID field and/or a user role field,and the search query includes one or both of a user ID and a role of theuser from which the search query originated.
 14. The computer programproduct of claim 11, wherein the activity data includes one of data in alast activity field and/or data in a last update field.
 15. Theecomputer program product of claim 11, further comprising computer codefor displaying at least a portion of the matching records in the order.